﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;

namespace EmployeeCheckIn
{
    public partial class ListAdmin : System.Web.UI.Page
    {
        StringBuilder sb = new StringBuilder();
        protected void Page_Load(object sender, EventArgs e)
        {
           
            if (IsPostBack)
            {
               
            }
                if (!IsPostBack)
                {
                    
                    BindingGrid();                     
                    lbWarn.Visible = false;
                    StringBuilder sb = new StringBuilder();
                    if (Session["User"] != null && Session["Pass"] != null)
                    {
                        // lbNameAdmin.Text = Session["User"].ToString();


                       
                        if (Session["updateadmin"] != null)
                        {
                            sb.Append("<script language='javascript' type = 'text/javascript'>");
                            sb.Append("alert('แก้ไขเรียบร้อย')");
                            sb.Append("</script>");
                            Response.Write(sb.ToString());
                            Session["updateadmin"] = null;
                            Session.Remove("updateadmin");
                            //Session["updateadmin"] = "";
                        }
                        Session["updateadmin"] = null;
                        Session.Remove("updateadmin");
                    }
                    else
                    {
                        Response.Redirect("Default.aspx");
                    }
                }


            }
        
        private DataTable GetData()
        {
            string sqltext = @"select user_name
                                     ,admin_id
                                     ,admin_name
                                     ,admin_lastname 
                                     ,admin_phone
                                     ,admin_date_regis
                                     ,admin_date_edit
                                     ,update_by                                      
                                     from tbl_log_in ";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        private void BindingGrid()
        {
            DataTable dt = GetData();
            gvAdminDetail.DataSource = dt;
            gvAdminDetail.DataBind();
            if (dt == null)
            {
                lbNotFound.Visible = true;
            }


        }
        public DateTime ConvestToDate(string s)
        {
            int day = 1;
            int mount = 1;
            int years = 1111;
            try
            {
                string[] stemp = s.Split('/');
                day = Convert.ToInt32(stemp[0]);
                mount = Convert.ToInt32(stemp[1]);
                years = Convert.ToInt32(stemp[2]);
                if (years > 2300)
                {
                    years = years - 543;
                }
                return new DateTime(years, mount, day);
            }
            catch (Exception)
            {

                //lbStdate.Visible = true;
                //lbStdate.Text = "*รูปแบบ วว-ดด-ปปปป";
                //lbStdate.Visible = true;
                //lbStdate.Text = "*รูปแบบ วว-ดด-ปปปป";
            }

            return new DateTime(years, mount, day);

        }
        private DataTable GetDataFromSert(string n,string tsd,string ted)
        {
            string sqltext = @"select user_name
                                     ,admin_id
                                     ,admin_name
                                     ,admin_lastname 
                                     ,admin_phone
                                     ,admin_date_regis
                                     ,admin_date_edit
                                     ,update_by                                      
                                     from tbl_log_in 
                                     WHERE (admin_name Like '" + n + "%'AND ADMIN_DATE_REGIS >= '" + tsd + "' and ADMIN_DATE_REGIS < '" + ted + "')OR(user_name Like '" + n + "%'AND ADMIN_DATE_REGIS >= '" + tsd + "' and ADMIN_DATE_REGIS < '" + ted + "')";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        private DataTable GetDataFromSert(string n)
        {
            string sqltext = @"select user_name
                                     ,admin_id
                                     ,admin_name
                                     ,admin_lastname 
                                     ,admin_phone
                                     ,admin_date_regis 
                                     ,admin_date_edit
                                     ,update_by                                     
                                     from tbl_log_in 
                                     WHERE (admin_name Like '" + n + "%')OR (user_name Like '" + n + "%')";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        private DataTable GetDataFromSert(string tsd, string ted)
        {
            string sqltext = @"select user_name
                                     ,admin_id
                                     ,admin_name
                                     ,admin_lastname 
                                     ,admin_phone
                                     ,admin_date_regis
                                     ,admin_date_edit
                                     ,update_by                                      
                                     from tbl_log_in 
                                     where ADMIN_DATE_REGIS >= '" + tsd+"' and ADMIN_DATE_REGIS < '"+ted+"'";
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
            SqlDataAdapter da = new SqlDataAdapter(sqltext, sqlconn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];

        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            bool resault = true;
            DataTable dt=GetData();
            lbNotFound.Visible = false;
            lbWarn.Visible = false;
            if (txtStDate.Text==""&&txtEnDate.Text==""&&txtNameAdmin.Text!="")
            {
                dt=GetDataFromSert(txtNameAdmin.Text);
                
            }
            if (txtNameAdmin.Text==""&&txtStDate.Text!=""&&txtEnDate.Text!="")
            {
                DateTime sd = ConvestToDate(txtStDate.Text);
                DateTime ed = ConvestToDate(txtEnDate.Text);
                string tsd = sd.ToString("yyyy-MM-dd 00:00:00.000", new System.Globalization.CultureInfo("en-US"));
                string ted = ed.ToString("yyyy-MM-dd 23:59:59.997", new System.Globalization.CultureInfo("en-US"));
                dt= GetDataFromSert(tsd, ted);
               
            }
            if (txtStDate.Text==""&&txtEnDate.Text!=""&&txtNameAdmin.Text!="")
            {
                lbWarn.Visible = true;
                resault = false;
              
            }
            if (txtStDate.Text != "" && txtEnDate.Text == ""&&txtNameAdmin.Text!="")
            {
                lbWarn.Visible = true;
                resault = false;
               
            }
            if (txtStDate.Text == "" && txtEnDate.Text != "" && txtNameAdmin.Text == "")
            {
                lbWarn.Visible = true;
                resault = false;
               
            }
            if (txtStDate.Text != "" && txtEnDate.Text == "" && txtNameAdmin.Text == "")
            {
                lbWarn.Visible = true;
                resault = false;
               
            }
            if (txtStDate.Text != "" && txtEnDate.Text != "" && txtNameAdmin.Text != "")
            {
                DateTime sd = ConvestToDate(txtStDate.Text);
                DateTime ed = ConvestToDate(txtEnDate.Text);
                string tsd = sd.ToString("yyyy-MM-dd 00:00:00.000", new System.Globalization.CultureInfo("en-US"));
                string ted = ed.ToString("yyyy-MM-dd 23:59:59.997", new System.Globalization.CultureInfo("en-US"));
                lbNotFound.Visible = false;
                dt = GetDataFromSert(txtNameAdmin.Text, tsd, ted);
            }
            if (resault == false)
            {
                return;
            }
             gvAdminDetail.DataSource = dt;
             if (!(dt != null && dt.Rows.Count > 0))
             {
                 lbNotFound.Visible = true;
             }
             gvAdminDetail.DataBind();
        }
        private void DelteteData(int i)
        {
            string sqltext = "DELETE FROM tbl_log_in WHERE admin_id = @i ";                     
                                 
            SqlConnection sqlconn = new SqlConnection("Server=202.44.52.248;Database=DB_Personal;User Id=personal;Password=1234;");
                       
            SqlCommand sqlcomm = new SqlCommand();
            sqlcomm.CommandText = sqltext;
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = CommandType.Text;
            sqlcomm.Parameters.AddWithValue("@i", i);
            try
            {
                sqlcomm.Connection.Open();
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception)
            {

                sqlcomm.Connection.Close();
                sqlcomm.Connection.Dispose();
            }
            sqlcomm.Connection.Close();
            sqlcomm.Connection.Dispose();

        }
        private void dataGridDel(int i)
        {
            int a = i;
            DelteteData(i);
            if (a != 0)
            {
                BindingGrid();                  
            }
        }

        protected void gvAdminDetail_RowCreated(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                e.Row.Attributes["onmouseover"] = "this.style.cursor='hand';this.style.backgroundColor='#6699FF';";
                e.Row.Attributes["onmouseout"] = "this.style.cursor='hand';this.style.backgroundColor='white';";

                // e.Row.Attributes["onclick"] = ClientScript.GetPostBackClientHyperlink(this.GridView1, "Select$" + e.Row.RowIndex);
            }
        }

        protected void gvAdminDetail_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            
            if (e.CommandName == "cmdDetail")
            {
                int i = Convert.ToInt32(e.CommandArgument.ToString());
                Session["Detailadmin_id"] = i;
                Server.Transfer("AdminDetail.aspx");
  
            }
            if (e.CommandName == "cmdEdit")
            {
                int i = Convert.ToInt32(e.CommandArgument.ToString());
                Session["AdminEdit_id"] = i;
                Server.Transfer("UpdateAdmin.aspx");
            }
            if (e.CommandName == "cmdDelete")
            {
                int i = Convert.ToInt32(e.CommandArgument.ToString());
                string row_id = i.ToString();
                string tIndex = Session["Index_id"].ToString();
                if (tIndex==row_id)
                {
                    Response.Write("<script>alert('ไม่สามารถลบได้!'); window.location='ListAdmin.aspx';</script>"); 
                }
                else
                {
                    Response.Write("<script>alert('ลบข้อมูลเรียบร้อยแล้ว!'); window.location='ListAdmin.aspx';</script>");
                    dataGridDel(i);
                }
                
            }
        }                    
        
       

        protected void Button2_Click(object sender, EventArgs e)
        {
            if (Session["User"] != null && Session["Pass"] != null)
            {
                Server.Transfer("CreateAdmin.aspx");
            }
            else
            {
                Response.Redirect("Default.aspx");
            }
        }
        public override void VerifyRenderingInServerForm(Control control) { }
        protected void Button3_Click(object sender, EventArgs e)
        {
            if (Session["User"] != null && Session["Pass"] != null)
            {
                if (gvAdminDetail.Visible == true)
                {
                    bool resault = true;
                    DataTable dt = GetData();
                    if (txtStDate.Text == "" && txtEnDate.Text == "" && txtNameAdmin.Text != "")
                    {
                        if (dt != null && dt.Rows.Count > 0)
                            dt = GetDataFromSert(txtNameAdmin.Text);
                    }
                    if (txtNameAdmin.Text == "" && txtStDate.Text != "" && txtEnDate.Text != "")
                    {
                        DateTime sd = ConvestToDate(txtStDate.Text);
                        DateTime ed = ConvestToDate(txtEnDate.Text);
                        string tsd = sd.ToString("yyyy-MM-dd 00:00:00.000", new System.Globalization.CultureInfo("en-US"));
                        string ted = ed.ToString("yyyy-MM-dd 23:59:59.997", new System.Globalization.CultureInfo("en-US"));
                        dt = GetDataFromSert(tsd, ted);
                    }
                    if (txtStDate.Text == "" && txtEnDate.Text != "" && txtNameAdmin.Text != "")
                    {
                        lbWarn.Visible = true;
                        resault = false;
                    }
                    if (txtStDate.Text != "" && txtEnDate.Text == "" && txtNameAdmin.Text != "")
                    {
                        lbWarn.Visible = true;
                        resault = false;
                    }
                    if (txtStDate.Text == "" && txtEnDate.Text != "" && txtNameAdmin.Text == "")
                    {
                        lbWarn.Visible = true;
                        resault = false;
                    }
                    if (txtStDate.Text != "" && txtEnDate.Text == "" && txtNameAdmin.Text == "")
                    {
                        lbWarn.Visible = true;
                        resault = false;
                    }
                    if (txtStDate.Text != "" && txtEnDate.Text != "" && txtNameAdmin.Text != "")
                    {
                        DateTime sd = ConvestToDate(txtStDate.Text);
                        DateTime ed = ConvestToDate(txtEnDate.Text);
                        string tsd = sd.ToString("yyyy-MM-dd 00:00:00.000", new System.Globalization.CultureInfo("en-US"));
                        string ted = ed.ToString("yyyy-MM-dd 23:59:59.997", new System.Globalization.CultureInfo("en-US"));
                        dt = GetDataFromSert(txtNameAdmin.Text, tsd, ted);
                    }
                    if (resault == false)
                    {
                        return;
                    }
                    Response.Clear();
                    Response.Buffer = true;
                    Response.AddHeader("content-disposition", "attachment;filename=ListAdmin.xls");
                    Response.Charset = "";
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    StringWriter sw = new StringWriter();
                    HtmlTextWriter hw = new HtmlTextWriter(sw);
                    gvAdminDetail.DataSource = dt;
                    gvAdminDetail.Columns[gvAdminDetail.HeaderRow.Cells.Count - 1].Visible = false;
                    gvAdminDetail.DataBind();
                    gvAdminDetail.AllowPaging = false;
                    gvAdminDetail.HeaderRow.Style.Add("background-color", "#FFFFFF");
                    for (int k = 0; k < gvAdminDetail.HeaderRow.Cells.Count; k++)
                    {
                        gvAdminDetail.HeaderRow.Cells[k].Style.Add("background-color", "#0099FF");

                    }
                    for (int i = 0; i < gvAdminDetail.Rows.Count; i++)
                    {

                        GridViewRow row = gvAdminDetail.Rows[i];

                        //Change Color back to white

                        row.BackColor = System.Drawing.Color.White;

                        //Apply text style to each Row

                        row.Attributes.Add("class", "textmode");
                        for (int j = 0; j < gvAdminDetail.HeaderRow.Cells.Count - 1; j++)
                        {
                            if (i % 2 != 0)
                            {
                                row.Cells[j].Style.Add("background-color", "#33CCFF");

                            }

                        }



                    }

                    gvAdminDetail.RenderControl(hw);

                    //style to format numbers to string

                    string style = @"<style> .textmode { mso-number-format:\@; } </style>";
                    Response.Write(style);
                    Response.Output.Write(sw.ToString());
                    Response.Flush();
                    Response.End();

                }

            }

        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            Response.Redirect("MenuAdmin.aspx");
        }
       
    }
}